package dbLayer;

import modelLayer.Schedule;
import modelLayer.TeachingBlock;
import modelLayer.Class;
import dbLayer.DBClass;
import dbLayer.DBTeachingBlock;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

public class DBSchedule {
	
	private Connection con;
	
	private DBClass dbClass = new DBClass();
	private DBTeachingBlock dbTeachingBlock = new DBTeachingBlock();
	
	public DBSchedule () {
		this.con = DBConnection.getInstance().getDBcon();
	}
	
	public List<Schedule> findScheduleBlockID (int classID) {
		
		ResultSet results;
		List<Schedule> matchingSchedules = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Schedules WHERE Class = ?");
			statement.setInt(1, classID);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				Schedule s = new Schedule();
				s = buildObject(results);
				System.out.println(s);
				matchingSchedules.add(s);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Query exceptio: " + e);
		}
		
		return matchingSchedules;
		
	}
	
	public boolean blankSlade (List<Class> classes) {
		
		boolean result = false;
		List<Integer> classIDs = new ArrayList<>();
			
			try {
				
				classIDs = checkScheduleClasses();
				
				for (Class cl: classes) {
					
					if (!classIDs.contains(cl.getID())) {
						
						int BlockNo = 1;
						
						for (int i = 0; i < BlockNo; ++i) {
							PreparedStatement statement = con.prepareStatement("INSERT INTO Schedules values (?,?,?)");
							statement.setInt(1, BlockNo);
							statement.setNull(2, java.sql.Types.INTEGER);
							statement.setInt(3, cl.getID());
							statement.executeUpdate();
							if (BlockNo < 30) {
								BlockNo = BlockNo + 1;
							}
						}
					}
				}
				
				result = true;
			}
			catch (Exception e) {
				System.out.println("Query exception: " + e);
			}
			
		return result;
	}
	
	public List<Schedule> tbAtBlockNo (int blockNo) {
		
		List<Schedule> matchingSchedules = new ArrayList<>();
		ResultSet results;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Schedules WHERE BlockNo = ?");
			statement.setInt(1, blockNo);
			statement.setQueryTimeout(5);
			
			results =  statement.executeQuery();
			
			while(results.next()) {
				
				Schedule s = new Schedule();
				s = buildObject(results);
				matchingSchedules.add(s);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
		return matchingSchedules;
		
	}
	
	public void addTBToSchedule (int classID, int blockNo, int tbID) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("UPDATE Schedules SET TeachingBlock = ? WHERE BlockNo = ? AND Class = ?");
			statement.setInt(1, tbID);
			statement.setInt(2, blockNo);
			statement.setInt(3, classID);
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}
	
	public void removeTBFromSchedule (int blockNo, int classID) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("UPDATE Schedules SET TeachingBlock = ? WHERE BlockNo = ? AND Class = ?");
			statement.setNull(1, java.sql.Types.INTEGER);
			statement.setInt(2, blockNo);
			statement.setInt(3, classID);
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}
	
	private Schedule buildObject(ResultSet results) {
		
		Schedule schedule = null;
		TeachingBlock tb = null;
		Class cl = null;
		
		try {
			
			if (results.getInt("TeachingBlock") != 0) {
				tb = dbTeachingBlock.findTeachingBlockID(results.getInt("TeachingBlock"));
			}
			
			if (results.getInt("Class") != 0) {
				cl = dbClass.findClassID(results.getInt("Class"));
			}
			
			schedule = new Schedule (results.getInt("ID"),
									 results.getInt("BlockNo"),
									 tb,
									 cl);
			
		}
		catch (Exception e) {
			System.out.println("Problems ocurred: " + e);
		}

		return schedule;
		
	}
	
	public List<Integer> checkScheduleClasses () {
		
		ResultSet results;
		List<Integer> classIDs = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT Class FROM Schedules");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				if (!classIDs.contains(results.getInt("Class"))) {
					
					classIDs.add(results.getInt("Class"));
					
				}
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		
		return classIDs;
		
	}

	public List<Schedule> getAllSchedules () {
		
		ResultSet results;
		List<Schedule> matchingSchedules = new ArrayList<>();
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Schedules");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while(results.next()) {
				
				Schedule schedule = new Schedule ();
				schedule = buildObject(results);
				System.out.println(schedule);
				matchingSchedules.add(schedule);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return matchingSchedules;
	}

}
